package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.StudentInfo;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Students table.
 */
public class StudentDb extends BaseDb {
    private String insert = "INSERT INTO students (name, birthday, male, class_id, description) VALUES (?, ?, ?, ?, ?)";
    private String update = "UPDATE students SET name = ?, birthday = ?, male = ?, class_id = ?, description = ?, active = ? WHERE id = ?";

    /**
     * Insert a Student into database.
     *
     * @param student row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(StudentInfo student) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            buildStatement(student);
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Student into database.
     *
     * @param student row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(StudentInfo student) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(student);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Students into database.
     *
     * @param students list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<StudentInfo> students) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (StudentInfo student : students) {
                doUpdate(student);
            }
        } finally {
            close();
        }
    }

    /**
     * Get an Student for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public StudentInfo select(int id) throws SQLException {
        String select = "SELECT * FROM students WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Students.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<StudentInfo> select() throws SQLException {
        return select(null);
    }

    /**
     * Get a list of Students.
     *
     * @param name the row's name to search.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<StudentInfo> select(String name) throws SQLException {
        return select(name, 0);
    }

    /**
     * Get a list of Students.
     *
     * @param name     the row's name to search. Null for all.
     * @param class_id the class's ID to search. Zero for all.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<StudentInfo> select(String name, int class_id) throws SQLException {
        String select = "SELECT * FROM students WHERE 1 = 1";
        if (name != null) select += " AND name LIKE '%" + name + "%'";
        if (class_id != 0) select += " AND class_id = " + class_id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<StudentInfo> list = new ArrayList<StudentInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param student the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(StudentInfo student) throws SQLException {
        buildStatement(student);
        st.setBoolean(6, student.isActive());
        st.setInt(7, student.getId());
        st.executeUpdate();
    }

    /**
     * Build the PreparedStatement from a data object.
     *
     * @param student the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void buildStatement(StudentInfo student) throws SQLException {
        st.setString(1, student.getName());
        st.setDate(2, student.getBirthday());
        st.setBoolean(3, student.isMale());
        st.setInt(4, student.getClassId());
        st.setString(5, student.getDescription());
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private StudentInfo getResult() throws SQLException {
        StudentInfo student = new StudentInfo();
        student.setId(rs.getInt(1));
        student.setName(rs.getString(2));
        student.setBirthday(rs.getDate(3));
        student.setMale(rs.getBoolean(4));
        student.setClassId(rs.getInt(5));
        student.setDescription(rs.getString(6));
        student.setActive(rs.getBoolean(7));

        return student;
    }
}
